CREATE OR REPLACE PROCEDURE WSO2_TOK_CLNUP_DT_RESTO_SP IS


rowCount INT;
CURRENT_SCHEMA VARCHAR(20);
-- ------------------------------------------
-- CONFIGURABLE ATTRIBUTES
-- ------------------------------------------
enableLog BOOLEAN := TRUE; -- ENABLE LOGGING [DEFAULT : TRUE]
logLevel VARCHAR(10):= 'TRACE'; -- SET LOG LEVELS : TRACE


BEGIN

SELECT SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) INTO CURRENT_SCHEMA FROM DUAL;



IF (enableLog)
THEN
    SELECT COUNT(*) INTO rowCount from ALL_TABLES where OWNER = CURRENT_SCHEMA AND table_name = upper('LOG_WSO2_TOK_CLN_DT_RESTR_SP');
    IF (rowCount = 1) then
    EXECUTE IMMEDIATE 'DROP TABLE LOG_WSO2_TOK_CLN_DT_RESTR_SP';
    COMMIT;
    END if;
    EXECUTE IMMEDIATE 'CREATE TABLE LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP VARCHAR(250) , LOG VARCHAR(250)) NOLOGGING';
    COMMIT;
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''WSO2_TOKEN_CLEANUP_DATA_RESTORATION_SP STARTED .... !'')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''USING SCHEMA :'||CURRENT_SCHEMA||''')';
    COMMIT;
END IF;


-- ---------------------

SELECT COUNT(1) INTO rowCount FROM ALL_TABLES where OWNER = CURRENT_SCHEMA AND TABLE_NAME IN ('IDN_OAUTH2_ACCESS_TOKEN');
IF (rowCount = 1)
THEN
    IF (enableLog AND logLevel IN ('TRACE')) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION STARTED ON IDN_OAUTH2_ACCESS_TOKEN TABLE !'')';
    COMMIT;
    END IF;
    EXECUTE IMMEDIATE 'INSERT INTO IDN_OAUTH2_ACCESS_TOKEN SELECT A.* FROM BAK_OAUTH2_ACCESS_TOKEN A LEFT JOIN IDN_OAUTH2_ACCESS_TOKEN B ON A.TOKEN_ID = B.TOKEN_ID WHERE B.TOKEN_ID IS NULL';
    rowCount:=  sql%Rowcount;
    IF (enableLog ) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON IDN_OAUTH2_ACCESS_TOKEN WITH '||rowCount||''')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
    END IF;
END IF;

-- ---------------------

SELECT COUNT(1) INTO rowCount FROM ALL_TABLES where OWNER = CURRENT_SCHEMA AND TABLE_NAME IN ('IDN_OAUTH2_AUTHORIZATION_CODE');
IF (rowCount = 1)
THEN
    IF (enableLog AND logLevel IN ('TRACE')) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION STARTED ON IDN_OAUTH2_AUTHORIZATION_CODE TABLE !'')';
    COMMIT;
    END IF;
    EXECUTE IMMEDIATE 'INSERT INTO IDN_OAUTH2_AUTHORIZATION_CODE SELECT A.* FROM BAK_OAUTH2_AUTHORIZATION_CODE A LEFT JOIN IDN_OAUTH2_AUTHORIZATION_CODE B ON A.CODE_ID = B.CODE_ID WHERE B.CODE_ID IS NULL';
    rowCount:=  sql%Rowcount;
    IF (enableLog ) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON IDN_OAUTH2_AUTHORIZATION_CODE WITH '||rowCount||''')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
    END IF;
END IF;

-- ---------------------

SELECT COUNT(1) INTO rowCount FROM ALL_TABLES where OWNER = CURRENT_SCHEMA AND TABLE_NAME IN ('IDN_OAUTH2_ACCESS_TOKEN_SCOPE');
IF (rowCount = 1)
THEN
    IF (enableLog AND logLevel IN ('TRACE')) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION STARTED ON IDN_OAUTH2_ACCESS_TOKEN_SCOPE TABLE !'')';
    COMMIT;
    END IF;
    EXECUTE IMMEDIATE 'INSERT INTO IDN_OAUTH2_ACCESS_TOKEN_SCOPE SELECT A.* FROM BAK_OAUTH2_ACCESS_TOKEN_SCOPE A LEFT JOIN IDN_OAUTH2_ACCESS_TOKEN_SCOPE B ON A.TOKEN_ID = B.TOKEN_ID AND A.TOKEN_SCOPE = B.TOKEN_SCOPE WHERE B.TOKEN_ID IS NULL';
    rowCount:=  sql%Rowcount;
    IF (enableLog ) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON IDN_OAUTH2_ACCESS_TOKEN_SCOPE WITH '||rowCount||''')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
    END IF;
END IF;

-- ---------------------

SELECT COUNT(1) INTO rowCount FROM ALL_TABLES where OWNER = CURRENT_SCHEMA AND TABLE_NAME IN ('IDN_OIDC_REQ_OBJECT_REFERENCE');
IF (rowCount = 1)
THEN
    IF (enableLog AND logLevel IN ('TRACE')) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION STARTED ON IDN_OIDC_REQ_OBJECT_REFERENCE TABLE !'')';
    COMMIT;
    END IF;
    EXECUTE IMMEDIATE 'INSERT INTO IDN_OIDC_REQ_OBJECT_REFERENCE SELECT A.* FROM BAK_OIDC_REQ_OBJECT_REFERENCE A LEFT JOIN IDN_OIDC_REQ_OBJECT_REFERENCE B ON A.ID = B.ID WHERE B.ID IS NULL';
    rowCount:=  sql%Rowcount;
    IF (enableLog ) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON IDN_OIDC_REQ_OBJECT_REFERENCE WITH '||rowCount||''')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
    END IF;
END IF;

-- --------------------

SELECT COUNT(1) INTO rowCount FROM ALL_TABLES where OWNER = CURRENT_SCHEMA AND TABLE_NAME IN ('IDN_OIDC_REQ_OBJECT_CLAIMS');
IF (rowCount = 1)
THEN
    IF (enableLog AND logLevel IN ('TRACE')) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION STARTED ON IDN_OIDC_REQ_OBJECT_CLAIMS TABLE !'')';
    COMMIT;
    END IF;
    EXECUTE IMMEDIATE 'INSERT INTO IDN_OIDC_REQ_OBJECT_CLAIMS SELECT A.* FROM BAK_OIDC_REQ_OBJECT_CLAIMS A LEFT JOIN IDN_OIDC_REQ_OBJECT_CLAIMS B ON A.ID = B.ID WHERE B.ID IS NULL';
    rowCount:=  sql%Rowcount;
    IF (enableLog ) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON IDN_OIDC_REQ_OBJECT_CLAIMS WITH '||rowCount||''')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
    END IF;
END IF;

-- -------------------

SELECT COUNT(1) INTO rowCount FROM ALL_TABLES where OWNER = CURRENT_SCHEMA AND TABLE_NAME IN ('IDN_OIDC_REQ_OBJ_CLAIM_VALUES');
IF (rowCount = 1)
THEN
    IF (enableLog AND logLevel IN ('TRACE')) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION STARTED ON IDN_OIDC_REQ_OBJ_CLAIM_VALUES TABLE !'')';
    COMMIT;
    END IF;
    EXECUTE IMMEDIATE 'INSERT INTO IDN_OIDC_REQ_OBJ_CLAIM_VALUES SELECT A.* FROM BAK_OIDC_REQ_OBJ_CLAIM_VALUES A LEFT JOIN IDN_OIDC_REQ_OBJ_CLAIM_VALUES B ON A.ID = B.ID WHERE B.ID IS NULL';
    rowCount:=  sql%Rowcount;
    IF (enableLog ) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON IDN_OIDC_REQ_OBJ_CLAIM_VALUES WITH '||rowcount||''')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
    END IF;
END IF;

IF (enableLog)
THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_TOK_CLN_DT_RESTR_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''WSO2_TOKEN_CLEANUP_DATA_RESTORATION_SP COMPLETED .... !'')';
    COMMIT;
END IF;

END;
